package logic;

import java.sql.*;
import java.util.*;

import dbconnection.*;

public class Filler {
	
	private Random random;
	private DBConnection db;
	private final String[] words = {"bla ", "k ", "Hallo ", "de ", "het ",
									"Hoe gaat het? ", "cake ", "blargh ", "Doei!! ", "en "};
	private final String dateYear = "2011";
	
	
	public Filler() throws SQLException{
				
		try {
			db = new DBConnection();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}		
		random = new Random();
		
	}


	public void fillDatabase(){
		// TODO Auto-generated method stub
		generalData();
		for(int i=0; i < (random.nextInt(500)+1000); i++){//generating customers
			generateBankAccount();
			String phonenumber = generateContract(i);
			for(int j=0; j<365;j++){//generating customer data
				runSQLStatement("INSERT INTO SMSBERICHT VALUES ("+ ((i*365)+j) + ", '" + phonenumber + "', '" + generatePhonenumber() + "', 3, '" + generateDate() + "', null, 'BLA')");
			}
		}
	}
	
	private void runSQLStatement(String query){
		try {
			db.runSQLStatement(query);
		} catch (SQLIntegrityConstraintViolationException e) {
			// TODO Auto-generated catch block
			System.out.println("Deze waarde is al geregistreerd in de database en daarom niet ingevoerd!");
		} catch (SQLSyntaxErrorException e) {
			// TODO Auto-generated catch block
			System.out.println("SQL syntaxfout!");
			e.printStackTrace();
		} catch (SQLException e){
			System.out.println("Oeps! SQL gaf een foutmelding:");
			e.printStackTrace();
		}
	}
	
	private String generateDate(){
		
		String dateformat;
		int month = random.nextInt(12)+1;
		
		
		int maxDays;
		
		if(month == 2){
			maxDays = 28;
		}else if(month%2 == 1 && month < 8){
			maxDays = 31;
		}else if(month%2 == 0 && month > 7){
			maxDays = 31;
		}else{
			maxDays = 30;
		}
		
		int date = random.nextInt(maxDays)+1;
		
		
		if(date <= 9){
			dateformat = ("0" + date + "/");
		}else{
			dateformat = (date + "/");
		}
		
		if(month <= 9){
			
			dateformat += ("0" + month + "/");
			
		}else{
			dateformat += (month + "/");
		}
		
		
		
		return (dateformat + dateYear);
	}
	
	private void generalData(){
		
		runSQLStatement("INSERT ALL INTO ABONNEMENTSVORM VALUES (1,'SMS BUDGET', 14.90, null, '4.95', null) " +
									"INTO ABONNEMENTSVORM VALUES (2, 'SMS Always', 29.90, null, '0', null) " +
									"INTO ABONNEMENTSVORM VALUES (3, 'Total Care', 40.90, null, '0', null) " +
									"SELECT * FROM DUAL");
		
		runSQLStatement("INSERT INTO FACTUUR VALUES(1, null, null, null)");
		
	}
	
	 private String generateContract(int id){
		  
		  String phonenumber = generatePhonenumber();
		  
		  String startDate = "01/01/2011";
		  String endDate = "12/01/2011";
		  String abID = new Integer((random.nextInt(3)+1)).toString();
		  
		  
		  runSQLStatement("INSERT INTO CONTRACT VALUES ('" + phonenumber + "', '" + startDate + "', '" + endDate +"', " + abID + ", " + "1)");
		  generateCustomer(phonenumber, id);
		  return phonenumber;
	 }
	
	
	private void generateCustomer(String phonenumber, int id){
		String sex = "";
		String postalCode = "";
		
		if(random.nextInt(2) < 1){
			sex = "M";
		}else{
			sex = "F";
		}
		
		/*for(int i = 0;i<4;i++){
			postalCode += new Integer(random.nextInt(10)).toString();
		}
		
		postalCode += 	new String(Character.toChars(random.nextInt(65338 - 65313) + 65313)) +
						new String(Character.toChars(random.nextInt(65338 - 65313) + 65313));*/
		postalCode = "9503KT";
		
		runSQLStatement("INSERT INTO KLANT VALUES ("+ id + ", '" + generateName() + "', '" + sex + "', " + "null, '" + postalCode + "', null, null, '" + phonenumber + "', '" + 
				generateBankAccount() + "')");

		
	}
	
	private String generateName(){

		/*char[] firstLetter = Character.toChars(random.nextInt(65338 - 65313) + 65313);
		
		String name = new String(firstLetter);
		name += ". " + new String(Character.toChars(random.nextInt(65338 - 65313) + 65313));
		
		int nameLength = random.nextInt(8) + 2;
		for(int i=0; i<nameLength; i++){
			name += Character.toChars(random.nextInt(65370 - 65345) + 65345);
		}
		
		*/return "blargh";
		
	}
	
	private String generateSMS(boolean coded){
		
		String sms = new String();
		int wordAmount = random.nextInt(11) + 1;
		
		for(int i=0; i<wordAmount; i++){ 
			sms += words[random.nextInt(words.length)];
		}
		
		if(coded){
			if(sms.length() % 2 == 1){
				sms = "0" + sms;
			}
			String smsCoded = new String();
			for(int i=0; i<sms.length(); i+=2){
				smsCoded += sms.charAt((i)+1);
				smsCoded += sms.charAt(i);
			}
			sms = smsCoded;
		}
		
		return sms;
		
	}
	
	private String generatePhonenumber(){
		
		String phonenumber = "06";
		for(int i=0;i<8;i++){
			phonenumber += random.nextInt(10);
		}
		
		return phonenumber;
	}
	
	private String generateBankAccount(){
		
		int number = random.nextInt(89999999) + 10000000;
		float budget = (random.nextInt(50000) + 100000)/100;
		
		runSQLStatement("INSERT INTO BANK VALUES (" + number + ", " + budget + ")");
		
		return new Integer(number).toString();
	}
	
	private boolean phoneNumberExists(){
		//TODO
		return true;
	}
	
	private String generateBill(){
		//TODO
		return null;
	}

}
